package com.liuhongwei.test.builder;



import com.alibaba.fastjson.JSONArray;
import com.liuhongwei.test.bean.Constants;
import com.liuhongwei.test.bean.FieldInfo;
import com.liuhongwei.test.bean.TableInfo;
import com.liuhongwei.test.utils.PropertiesUtils;
import lombok.extern.slf4j.Slf4j;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
public class BuildTable {

    private static final Logger logger = LoggerFactory.getLogger(BuildTable.class);
    private static Connection connection = null;

    private static final String SQL_SHOW_DATA_STATUS = "show table status;";
    private static String SQL_SHOW_TABLE_FIELDS = "show full FIELDS from %s";
    private static String SQL_SHOW_TABLE_INDEX = "show index from %s";

    static {
        String driverName = PropertiesUtils.getProperty("jdbc.connection.driver");
        String url = PropertiesUtils.getProperty("jdbc.connection.url");
        String username = PropertiesUtils.getProperty("jdbc.connection.user");
        String password = PropertiesUtils.getProperty("jdbc.connection.pwd");

        try {
            Class.forName(driverName);
            connection = DriverManager.getConnection(url, username, password);

        } catch (Exception e) {
            logger.error("数据库连接失败", e);
            throw new RuntimeException(e);
        }

    }

    public static List<TableInfo> getTable() {
        return BuildTable.getTable("tb_sys_country", "国家表");

        /*
        PreparedStatement ps = null;
        ResultSet rs = null;

        List<TableInfo> tableInfoList = new ArrayList<>();
        try {
            ps = connection.prepareStatement(SQL_SHOW_DATA_STATUS);
            rs = ps.executeQuery();
            while (rs.next()) {
                String tableName = rs.getString("name");
                String comment = rs.getString("comment");
                log.info("tableName:{}，comment:{}", tableName, comment);

                TableInfo tableInfo = new TableInfo();
                tableInfo.setTableName(tableName);
                String beanName = tableName;
                if (Constants.IGNORE_TABLE_PERFIX) {
                    beanName = beanName.substring(beanName.indexOf("_") + 1);
                }
                beanName = processField(beanName, true);
                tableInfo.setBeanName(beanName);
                tableInfo.setComment(comment);
                tableInfo.setBeanParamName(beanName + Constants.SUFFIX_BEAN_QUERY);

                // 读取字段信息
                readFieldInfo(tableInfo);
                // 读取索引信息
                readKeyIndexInfo(tableInfo);
//
                tableInfoList.add(tableInfo);
            }
        } catch (Exception e) {
            logger.error("获取表结构失败", e);
        } finally {
            close(ps, rs);

        }

        return tableInfoList;

         */
    }

    public static List<TableInfo> getTable(String tableName, String comment) {
        PreparedStatement ps = null;
        ResultSet rs = null;

        List<TableInfo> tableInfoList = new ArrayList<>();
        try {
            log.info("tableName:{}，comment:{}", tableName, comment);

            TableInfo tableInfo = new TableInfo();
            tableInfo.setTableName(tableName);
            String beanName = tableName;
            if (Constants.IGNORE_TABLE_PERFIX) {
                beanName = beanName.substring(beanName.indexOf("_") + 1);
            }
            beanName = processField(beanName, true);
            tableInfo.setBeanName(beanName);
            tableInfo.setComment(comment);
            tableInfo.setBeanParamName(beanName + Constants.SUFFIX_BEAN_QUERY);

            // 读取字段信息
            readFieldInfo(tableInfo);
            // 读取索引信息
            readKeyIndexInfo(tableInfo);
//
            tableInfoList.add(tableInfo);
        } catch (Exception e) {
            logger.error("获取表结构失败", e);
        } finally {
            close(ps, rs);

        }

        return tableInfoList;
    }


    private static void close(PreparedStatement ps, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                logger.error("关闭结果集失败", e);
            }
        }

        if (ps != null) {
            try {
                ps.close();
            } catch (Exception e) {
                logger.error("关闭预处理语句失败", e);
            }
        }
    }

    /**
     * 将下划线命名的字符串转换为驼峰命名法。
     * 根据 upperFirstLetter 参数决定首字母是否大写。
     *
     * @param field            下划线命名的字符串
     * @param upperFirstLetter 首字母是否大写的标志
     * @return 转换后的驼峰命名的字符串
     */
    private static String processField(String field, Boolean upperFirstLetter) {
        if (field == null || field.isEmpty()) {
            return field;
        }
        StringBuilder result = new StringBuilder();
        String[] parts = field.split("_");
        for (int i = 0; i < parts.length; i++) {
            String part = parts[i];
            if (part.isEmpty()) {
                continue;
            }
            if (i == 0 && !upperFirstLetter) {
                result.append(part.toLowerCase());
            } else {
                result.append(Character.toUpperCase(part.charAt(0))).append(part.substring(1).toLowerCase());
            }
        }
        return result.toString();
    }


    private static void readFieldInfo(TableInfo tableInfo) {
        PreparedStatement ps = null;
        ResultSet rs = null;

        // 字段信息
        List<FieldInfo> fieldInfoList = new ArrayList<>();
        // 字段扩展信息，用于查询数据使用
        List<FieldInfo> fieldExtendList = new ArrayList<>();
        try {
            ps = connection.prepareStatement(String.format(SQL_SHOW_TABLE_FIELDS, tableInfo.getTableName()));
            rs = ps.executeQuery();
            while (rs.next()) {
                String field = rs.getString("field");
                String type = rs.getString("type");
                String extra = rs.getString("extra");
                String comment = rs.getString("comment");
                String propertyName = processField(field, false);
                if (type.indexOf("(") > 0) {
                    type = type.substring(0, type.indexOf("("));
                }

                FieldInfo fieldInfo = new FieldInfo();
                fieldInfo.setFieldName(field);
                fieldInfo.setSqlType(type);
                fieldInfo.setComment(comment);
                fieldInfo.setAutoIncrement("auto_increment".equalsIgnoreCase(extra) ? true : false);
                fieldInfo.setPropertyName(propertyName);
                fieldInfo.setJavaType(processJavaType(type));

                if (Constants.SQL_DATE_TIME_TYPES.contains(type)) {
                    tableInfo.setHaveDateTime(true);
                }
                if (Constants.SQL_DATE_TYPES.contains(type)) {
                    tableInfo.setHaveDate(true);
                }
                if (Constants.SQL_DECIMAL_TYPES.contains(type)) {
                    tableInfo.setHaveBigDecimal(true);
                }

                // String类型的参数
                if (fieldInfo.getJavaType().equals(Constants.TYPE_STRING)) {
                    buildProperty(fieldExtendList, propertyName + Constants.SUFFIX_BEAN_QUERY_FUZZY, field, type);
                }

                // 日期类型的参数
                if (fieldInfo.getJavaType().equals(Constants.TYPE_DATE)) {
                    buildProperty(fieldExtendList, propertyName + Constants.SUFFIX_BEAN_QUERY_TIME_START, field, type);
                    buildProperty(fieldExtendList, propertyName + Constants.SUFFIX_BEAN_QUERY_TIME_END, field, type);
                }

                fieldInfoList.add(fieldInfo);
            }

            // 循环的外面设置fieldInfoList
            tableInfo.setFieldList(fieldInfoList);
            // 循环的外面设置fieldExtendList
            tableInfo.setFieldExtendList(fieldExtendList);
        } catch (Exception e) {
            log.error("获取字段失败", e);
        } finally {
            close(ps, rs);
        }
    }


    private static String processJavaType(String type) {
        if (Constants.sqlTypeMap.containsKey(type)) {
            return Constants.sqlTypeMap.get(type);
        } else {
            throw new RuntimeException("无法识别的类型：" + type);
        }
    }

    private static void buildProperty(List<FieldInfo> fieldList, String propertyName, String field, String type) {
        FieldInfo fieldInfo = new FieldInfo();
        fieldInfo.setFieldName(field);
        fieldInfo.setJavaType(processJavaType(type));
        fieldInfo.setPropertyName(propertyName);
        fieldInfo.setSqlType(type);
        fieldList.add(fieldInfo);
    }


    private static void readKeyIndexInfo(TableInfo tableInfo) {
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Map<String, FieldInfo> tempMap = new HashMap<>();
            for (FieldInfo fieldInfo : tableInfo.getFieldList()) {
                tempMap.put(fieldInfo.getFieldName(), fieldInfo);
            }

            ps = connection.prepareStatement(String.format(SQL_SHOW_TABLE_INDEX, tableInfo.getTableName()));
            rs = ps.executeQuery();
            while (rs.next()) {
                String keyName = rs.getString("key_name");
                int nonUnique = rs.getInt("non_unique");
                String columnName = rs.getString("column_name");

                if (nonUnique == 1) {
                    continue;
                }
                List<FieldInfo> fieldInfos = tableInfo.getKeyIndexMap().computeIfAbsent(keyName, k -> new ArrayList<>());
                if (tempMap.containsKey(columnName)) {
                    fieldInfos.add(tempMap.get(columnName));
                }
            }
        } catch (Exception e) {
            log.error("获取索引失败", e);
        } finally {
            close(ps, rs);
        }
    }


    public static void main(String[] args) {
        List<TableInfo> tableInfoList = getTable();

    }
}
